<?php

namespace ServiceSdk\Utils\Excel;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

/**
 *    string manager
 *
 */
class ExcelHandler
{
    private static $instance = null;

    public static function getInstance()
    {
        if (is_null(self::$instance) || ! self::$instance instanceof ExcelHandler) {
            self::$instance = new static();
        }

        return self::$instance;
    }

    /**
     * 使用PHPEXECL导入
     *
     * @param string $file      文件地址
     * @param int $sheet        工作表sheet(传0则获取第一个sheet)
     * @param int $columnCnt    列数(传0则自动获取最大列)
     * @param array $options    操作选项
     *                          array mergeCells 合并单元格数组
     *                          array formula    公式数组
     *                          array format     单元格格式数组
     *
     * @return array
     * @throws Exception
     */
    public static function importExecl(string $file = '', int $sheet = 0, int $columnCnt = 0, &$options = [])
    {
        try {
            /* 转码 */
            $file = iconv("utf-8", "gb2312", $file);

            if (empty($file) OR ! file_exists($file)) {
                throw new \Exception('文件不存在!');
            }

            /** @var Xlsx $objRead */
            $objRead = IOFactory::createReader('Xlsx');

            if (! $objRead->canRead($file)) {
                /** @var Xls $objRead */
                $objRead = IOFactory::createReader('Xls');

                if (! $objRead->canRead($file)) {
                    throw new \Exception('只支持导入Excel文件！');
                }
            }

            /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
            empty($options) && $objRead->setReadDataOnly(true);
            /* 建立excel对象 */
            $obj = $objRead->load($file);
            /* 获取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);

            if (isset($options['mergeCells'])) {
                /* 读取合并行列 */
                $options['mergeCells'] = $currSheet->getMergeCells();
            }

            if (0 == $columnCnt) {
                /* 取得最大的列号 */
                $columnH = $currSheet->getHighestColumn();
                /* 兼容原逻辑，循环时使用的是小于等于 */
                $columnCnt = Coordinate::columnIndexFromString($columnH);
            }

            /* 获取总行数 */
            $rowCnt = $currSheet->getHighestRow();
            $data = [];

            /* 读取内容 */
            for ($_row = 1; $_row <= $rowCnt; $_row++) {
                $isNull = true;

                for ($_column = 1; $_column <= $columnCnt; $_column++) {
                    $cellName = Coordinate::stringFromColumnIndex($_column);
                    $cellId = $cellName . $_row;
                    $cell = $currSheet->getCell($cellId);

                    if (isset($options['format'])) {
                        /* 获取格式 */
                        $format = $cell->getStyle()
                                       ->getNumberFormat()
                                       ->getFormatCode();
                        /* 记录格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }

                    if (isset($options['formula'])) {
                        /* 获取公式，公式均为=号开头数据 */
                        $formula = $currSheet->getCell($cellId)
                                             ->getValue();

                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }

                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻转处理 */
                        $cell->getStyle()
                             ->getNumberFormat()
                             ->setFormatCode('yyyy/mm/dd');
                    }

                    $data[$_row][$cellName] = trim($currSheet->getCell($cellId)
                                                             ->getFormattedValue());

                    if (! empty($data[$_row][$cellName])) {
                        $isNull = false;
                    }
                }

                /* 判断是否整行数据为空，是的话删除该行数据 */
                if ($isNull) {
                    unset($data[$_row]);
                }
            }

            return $data;
        } catch (\Exception $e) {
            throw $e;
        }
    }

    /**
     * Excel导出，TODO 可继续优化
     *
     * @param array $datas       导出数据，格式['A1' => 'XXXX公司报表', 'B1' => '序号']
     * @param string $fileName   导出文件名称
     * @param array $options     操作选项，例如：
     *                           bool   print       设置打印格式
     *                           string freezePane  锁定行数，例如表头为第一行，则锁定表头输入A2
     *                           array  setARGB     设置背景色，例如['A1', 'C1']
     *                           array  setWidth    设置宽度，例如['A' => 30, 'C' => 20]
     *                           bool   setBorder   设置单元格边框
     *                           array  mergeCells  设置合并单元格，例如['A1:J1' => 'A1:J1']
     *                           array  formula     设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
     *                           array  format      设置格式，整列设置，例如['A' => 'General']
     *                           array  alignCenter 设置居中样式，例如['A1', 'A2']
     *                           array  bold        设置加粗样式，例如['A1', 'A2']
     *                           string savePath    保存路径，设置后则文件保存到服务器，不通过浏览器下载
     *
     * @return bool
     * @throws \Exception
     */
    public function exportExcel(array $datas, string $fileName = '', array $options = []): bool
    {
        try {
            if (empty($datas)) {
                return false;
            }

            set_time_limit(0);
            /** @var Spreadsheet $objSpreadsheet */
            $objSpreadsheet = app(Spreadsheet::class);
            /* 设置默认文字居左，上下居中 */
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical'   => Alignment::VERTICAL_CENTER,
                ],
                'font'      => [
                    'name' => $options['setDefaultFontName'] ?? '微软雅黑',
                    'size' => 12
                ]
            ];

            // 设置默认列宽
            if (isset($options['setDefaultWidth']) && is_numeric($options['setDefaultWidth'])) {
                $objSpreadsheet->getActiveSheet()
                               ->getDefaultColumnDimension()
                               ->setWidth($options['setDefaultWidth']);
            } else {
                $objSpreadsheet->getActiveSheet()
                               ->getDefaultColumnDimension()
                               ->setAutoSize(true);
            }
            // 设置默认行高
            $objSpreadsheet->getActiveSheet()
                           ->getDefaultRowDimension()
                           ->setRowHeight($options['setDefaultHeight'] ?? 20);

            $objSpreadsheet->getDefaultStyle()
                           ->applyFromArray($styleArray);

            /* 设置Excel Sheet */
            $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);

            /* 打印设置 */
            if (isset($options['print']) && $options['print']) {
                /* 设置打印为A4效果 */
                $activeSheet->getPageSetup()
                            ->setPaperSize(PageSetup:: PAPERSIZE_A4);
                /* 设置打印时边距 */
                $pValue = 1 / 2.54;
                $activeSheet->getPageMargins()
                            ->setTop($pValue / 2);
                $activeSheet->getPageMargins()
                            ->setBottom($pValue * 2);
                $activeSheet->getPageMargins()
                            ->setLeft($pValue / 2);
                $activeSheet->getPageMargins()
                            ->setRight($pValue / 2);
            }

            /* 行数据处理 */
            foreach ($datas as $sKey => $sItem) {
                /* 默认文本格式 */
                $pDataType = DataType::TYPE_STRING;
                /* 设置单元格格式 */
                if (isset($options['format']) && ! empty($options['format'])) {
                    $colRow = Coordinate::coordinateFromString($sKey);

                    /* 存在该列格式并且有特殊格式 */
                    if (isset($options['format'][$colRow[0]]) && NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
                        $activeSheet->getStyle($sKey)
                                    ->getNumberFormat()
                                    ->setFormatCode($options['format'][$colRow[0]]);

                        if (false !== strpos($options['format'][$colRow[0]], '0.00') && is_numeric(str_replace(['￥', ','], '', $sItem))) {
                            /* 数字格式转换为数字单元格 */
                            $pDataType = DataType::TYPE_NUMERIC;
                            $sItem = str_replace(['￥', ','], '', $sItem);
                        }
                    } elseif (is_int($sItem)) {
                        $pDataType = DataType::TYPE_NUMERIC;
                    }
                }

                $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);

                /* 存在:形式的合并行列，列入A1:B2，则对应合并 */
                if (false !== strstr($sKey, ":")) {
                    $options['mergeCells'][$sKey] = $sKey;
                }
            }

            /* 设置锁定行 */
            if (isset($options['freezePane']) && ! empty($options['freezePane'])) {
                $activeSheet->freezePane($options['freezePane']);
                unset($options['freezePane']);
            }

            /* 设置宽度 */
            if (isset($options['setWidth']) && ! empty($options['setWidth'])) {
                foreach ($options['setWidth'] as $swKey => $swItem) {
                    $activeSheet->getColumnDimension($swKey)
                                ->setWidth($swItem);
                }

                unset($options['setWidth']);
            }

            /* 设置高度 */
            if (isset($options['setHeight']) && ! empty($options['setHeight'])) {
                foreach ($options['setHeight'] as $row => $value) {
                    if (is_numeric($value) && $value > 0) {
                        $activeSheet->getRowDimension($row)
                                    ->setRowHeight($value);
                    }
                }

                unset($options['setWidth']);
            }

            // 设置超链接
            if (isset($options['setHyperlink']) && ! empty($options['setHyperlink']) && isset($options['setHyperlink']['column'])) {
                foreach ($options['setHyperlink']['column'] as $hlItem) {
                    foreach ($datas as $sKey => $sItem) {
                        if (isset($options['setHyperlink']['skip']) && in_array($sKey, $options['setHyperlink']['skip'])) {
                            continue;
                        }
                        if (stripos($sKey, $hlItem) !== false) {
                            $url = $activeSheet->getCell($sKey)
                                               ->getValue();
                            $text = "点击查看";
                            $activeSheet->setCellValue($sKey, $text);
                            $activeSheet->getCell($sKey)
                                        ->getHyperlink()
                                        ->setUrl($url);
                        }
                    }
                }

                unset($options['setWidth']);
            }

            // 设置远程图片显示
            if (isset($options['setImage']) && ! empty($options['setImage']) && isset($options['setImage']['column'])) {
                $imageRoot = storage_path('temp/' . date('YmdHis') . '/');
                if (! is_dir($imageRoot)) {
                    @mkdir($imageRoot, 0777, true);
                }
                foreach ($options['setImage']['column'] as $hlItem) {
                    foreach ($datas as $sKey => $sItem) {
                        if (isset($options['setImage']['skip']) && in_array($sKey, $options['setImage']['skip'])) {
                            continue;
                        }
                        if (stripos($sKey, $hlItem) !== false) {
                            $url = $activeSheet->getCell($sKey)
                                               ->getValue();
                            if (! empty($url)) {
                                $suffix = pathinfo(basename($url), PATHINFO_EXTENSION);
                                $imageName = md5(base64_encode(basename($url))) . '.' . $suffix;
                                $imagePath = $imageRoot . $imageName;

                                if ($this->downloadImage($url . '@!middle_square', $imagePath)) {
                                    // 图片下载成功，则直接植入图片
                                    if (is_file($imagePath)) {
                                        if (@fopen($imagePath, "r")) {
                                            // echo 123;die();
                                            if (preg_match('/([A-Z])(\d+)/', $sKey, $match)) {
                                                $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();//坑1 每次循环一遍都要实例化一次PHPExcel_Worksheet_Drawing()对象
                                                $objDrawing->setPath($imagePath);//设置图片在服务器的绝对地址
                                                $objDrawing->setHeight(80);//设置图片高度
                                                $objDrawing->setWidth(100);//设置图片宽度
                                                $objDrawing->setOffsetX(10);//设置图片水平偏移
                                                $objDrawing->setOffsetY(5);//设置图片垂直偏移

                                                $objDrawing->setCoordinates($sKey);
                                                unset($match[0]);
                                                $match = array_values($match);
                                                list($colum, $row) = $match;
                                                if (! isset($options['setImage']['cellStyle']) || $options['setImage']['cellStyle'] !== false) {
                                                    $objSpreadsheet->getActiveSheet()
                                                                   ->getRowDimension($row)
                                                                   ->setRowHeight(85);//设置行高
                                                    $objSpreadsheet->getActiveSheet()
                                                                   ->getColumnDimension($colum)
                                                                   ->setWidth(18);//设置列宽
                                                }
                                                $activeSheet->setCellValue($sKey, '');
                                                $objDrawing->setWorksheet($objSpreadsheet->getActiveSheet());
                                            }
                                        }
                                    } else {
                                        $text = "点击查看";
                                        $activeSheet->setCellValue($sKey, $text);
                                        $activeSheet->getCell($sKey)
                                                    ->getHyperlink()
                                                    ->setUrl($url);
                                    }
                                } else {
                                    // 图片没下载成功，则使用原始的超链接
                                    $text = "点击查看";
                                    $activeSheet->setCellValue($sKey, $text);
                                    $activeSheet->getCell($sKey)
                                                ->getHyperlink()
                                                ->setUrl($url);
                                }
                            }
                        }
                    }
                }

                unset($options['setWidth']);
            }

            // 填充本地图片，即指定本地的图片放进excel
            if (! empty($options['setLocalImage']) && ! empty($options['setLocalImage']['column']) && ! empty($options['setLocalImage']['image_set']['image_path']) && is_file($options['setLocalImage']['image_set']['image_path'])) {
                foreach ($options['setLocalImage']['column'] as $cell) {
                    if (@fopen($options['setLocalImage']['image_set']['image_path'], "r")) {
                        // echo 123;die();
                        if (preg_match('/([A-Z])(\d+)/', $cell, $match)) {
                            $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();//坑1 每次循环一遍都要实例化一次PHPExcel_Worksheet_Drawing()对象
                            $objDrawing->setPath($options['setLocalImage']['image_set']['image_path']);//设置图片在服务器的绝对地址
                            $objDrawing->setHeight($options['setLocalImage']['image_set']['image_height'] ?? 70);//设置图片高度
                            $objDrawing->setWidth($options['setLocalImage']['image_set']['image_width'] ?? 220);//设置图片宽度
                            $objDrawing->setOffsetX($options['setLocalImage']['image_set']['image_offset_x'] ?? 0);//设置图片水平偏移
                            $objDrawing->setOffsetY($options['setLocalImage']['image_set']['image_offset_y'] ?? 0);//设置图片垂直偏移

                            $objDrawing->setCoordinates($cell);
                            unset($match[0]);
                            $match = array_values($match);
                            list($colum, $row) = $match;
                            /*$objSpreadsheet->getActiveSheet()
                                           ->getRowDimension($row)
                                           ->setRowHeight(85);//设置行高
                            $objSpreadsheet->getActiveSheet()
                                           ->getColumnDimension($colum)
                                           ->setWidth(18);//设置列宽*/
                            $activeSheet->setCellValue($sKey, '');
                            $objDrawing->setWorksheet($objSpreadsheet->getActiveSheet());
                        }
                    }
                }
            }
            /* 设置背景色 */
            if (isset($options['setARGB']) && ! empty($options['setARGB'])) {
                foreach ($options['setARGB'] as $sItem) {
                    $activeSheet->getStyle($sItem)
                                ->getFill()
                                ->setFillType(Fill::FILL_SOLID)
                                ->getStartColor()
                                ->setARGB(Color::COLOR_YELLOW);
                }

                unset($options['setARGB']);
            }

            /* 设置公式 */
            if (isset($options['formula']) && ! empty($options['formula'])) {
                foreach ($options['formula'] as $fKey => $fItem) {
                    $activeSheet->setCellValue($fKey, $fItem);
                }

                unset($options['formula']);
            }

            /* 合并行列处理 */
            if (isset($options['mergeCells']) && ! empty($options['mergeCells'])) {
                $activeSheet->setMergeCells($options['mergeCells']);
                unset($options['mergeCells']);
            }

            /* 设置居中 */
            if (isset($options['alignCenter']) && ! empty($options['alignCenter'])) {
                $styleArray = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical'   => Alignment::VERTICAL_CENTER,
                    ],
                ];

                foreach ($options['alignCenter'] as $acItem) {
                    $activeSheet->getStyle($acItem)
                                ->applyFromArray($styleArray);
                }

                unset($options['alignCenter']);
            }

            /* 设置字体大小 */
            if (isset($options['fontSize']) && ! empty($options['fontSize'])) {
                $styleArray = [
                    'font' => [
                        'size' => 0,
                    ],
                ];
                foreach ($options['fontSize'] as $acItem => $size) {
                    $styleArray['font']['size'] = $size;
                    $activeSheet->getStyle($acItem)
                                ->applyFromArray($styleArray);
                }

                unset($options['fontSize']);
            }

            /* 设置字体名称，如宋体 */
            if (isset($options['fontName']) && ! empty($options['fontName'])) {
                foreach ($options['fontSize'] as $acItem => $name) {
                    $activeSheet->getStyle($acItem)
                                ->getFont()
                                ->setName($name);
                }

                unset($options['fontName']);
            }

            /* 设置字体颜色，如红色 FFFFFF00 */
            if (isset($options['fontColor']) && ! empty($options['fontColor'])) {
                foreach ($options['fontColor'] as $acItem => $color) {
                    $activeSheet->getStyle($acItem)
                                ->getFont()
                                ->getColor()
                                ->setARGB($color);
                }

                unset($options['fontColor']);
            }

            /* 设置加粗 */
            if (isset($options['bold']) && ! empty($options['bold'])) {
                foreach ($options['bold'] as $bItem) {
                    $activeSheet->getStyle($bItem)
                                ->getFont()
                                ->setBold(true);
                }

                unset($options['bold']);
            }

            /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
            if (isset($options['setBorder']) && $options['setBorder']) {
                $border = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN, // 设置border样式
                            'color'       => ['argb' => 'FF000000'], // 设置border颜色
                        ],
                    ],
                ];
                $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
                $activeSheet->getStyle($setBorder)
                            ->applyFromArray($border);
                unset($options['setBorder']);
            }

            // 设置自动换行
            if (isset($options['wrapText']) && ! empty($options['wrapText'])) {
                foreach ($options['wrapText'] as $cell) {
                    $activeSheet->getStyle($cell)
                                ->getAlignment()
                                ->setWrapText(true);
                }

                unset($options['wrapText']);
            }

            unset($datas);

            $fileName = ! empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');

            if (! isset($options['savePath'])) {
                /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header("Content-Disposition:attachment;filename=" . $fileName);
                header('Cache-Control: max-age=0');//禁止缓存
                $savePath = 'php://output';
            } else {
                $savePath = $options['savePath'];
            }

            ob_clean();
            ob_start();
            $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
            $objWriter->save($savePath);
            /* 释放内存 */
            $objSpreadsheet->disconnectWorksheets();
            unset($objSpreadsheet);
            ob_end_flush();
            if (! empty($imageRoot)) {
                // 如果有图片下载，则就删除原始图片
                $this->removeDirAndFile($imageRoot);
            }

            return true;
        } catch (\Exception $e) {
            if (! empty($imageRoot)) {
                // 如果有图片下载，则就删除原始图片
                $this->removeDirAndFile($imageRoot);
            }

            dump($e->getMessage());
            dump($e->getFile());
            dump($e->getLine());
            exit;

            return false;
        }
    }

    private function downloadImage($imageUrl, $imagePath)
    {
        $success = true;
        try {
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'GET');
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_URL, $imageUrl);

            ob_start();
            curl_exec($ch);
            $return_content = ob_get_contents();
            ob_end_clean();

            $return_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $fp = @fopen($imagePath, "w");
            fwrite($fp, $return_content);
            @fclose($fp);
        } catch (\Exception $exception) {
            $success = false;
        }

        return $success;
    }

    private function removeDirAndFile($dirName)
    {
        if ($handle = @opendir("$dirName")) {
            while (false !== ($item = @readdir($handle))) {
                if ($item != "." && $item != "..") {
                    if (is_dir("$dirName/$item")) {
                        $this->removeDirAndFile("$dirName/$item");
                    } else {
                        @unlink("$dirName/$item");
                    }
                }
            }
            closedir($handle);
            @rmdir($dirName);
        }
    }

    /**
     * 生成excel导入的格式
     *
     * @param $data array 二维数组
     *
     * @return array
     */
    public function generateExcelFormat($data)
    {
        $column = ord("A");
        $newData = [];
        foreach ($data as $row => $datum) {
            foreach ($datum as $key => $item) {
                // 这里是根据数据量决定 列字母，比如炒股26个，就应该是AA 而不是之前的直接吧ascii转为字符这么简单了
                if ($key < 26) {
                    $columnKey = chr($column + $key);
                } else if ($key < 702) {
                    $columnKey = chr($column - 1 + ($key / 26)) . chr($column + $key % 26);
                } else {
                    $columnKey = chr($column - 1 + (($key - 26) / 676)) . chr($column + ((($key - 26) % 676) / 26)) . chr(65 + $column % 26);
                }

                $newData[$columnKey . ($row + 1)] = $item;
            }
        }

        return $newData;
    }


    //调用示例（demo）
    private function createOrderNotContainCostInfoListReportContent(array $orderInfo): string
    {
        $redisKey = '';
        if (! empty($orderInfo['order_list'])) {
            $filterSpaceElement = function ($num, $value = '') {
                return array_fill(0, $num, $value);
            };
            // 有些信息是每个子产品都有的，因此这里取第一个子产品的信息就够了，例如客户信息，订单号等等
            $order = $orderInfo['order_list'][0];
            $orderAddon = $order['extension_data'];
            $customerInfo = $order['customer_info'];
            $customerInfo['customer_manager_phone'] = $customerInfo['designer_phone'] = $customerInfo['project_manager_phone'] = '';
            if (! empty($customerInfo['customer_manager'])) {
                $user = $this->getAdminUserInfo($customerInfo['customer_manager'], $order['company_id']);
                $customerInfo['customer_manager_phone'] = $user['phone'] ?? '';
                unset($user);
            }
            if (! empty($customerInfo['designer'])) {
                $user = $this->getAdminUserInfo($customerInfo['designer'], $order['company_id']);
                $customerInfo['designer_phone'] = $user['phone'] ?? '';
                unset($user);
            }
            if (! empty($customerInfo['project_manager'])) {
                $user = $this->getAdminUserInfo($customerInfo['project_manager'], $order['company_id']);
                $customerInfo['project_manager_phone'] = $user['phone'] ?? '';
                unset($user);
            }

            // 必须有数据才行
            $data = [
                // 第一行留空
                $filterSpaceElement(28),
                // 第二行做标题居中
                array_merge(['定制家具下单明细表'], $filterSpaceElement(27)),
                // 第三行留空
                $filterSpaceElement(28),
                // 第四行留空
                $filterSpaceElement(28),
                // 第五行订单基本信息
                array_merge(['', '订单号：', '', $order['sn']], $filterSpaceElement(9), ['客户姓名：', '', $order['user_name']], $filterSpaceElement(12)),
                // 第六行留空
                $filterSpaceElement(28),
                // 第七行跟踪人信息
                array_merge(['', '客户经理：', '', $customerInfo['customer_manager']], $filterSpaceElement(9), ['设计师：', '', $customerInfo['designer']], $filterSpaceElement(6), ['项目经理：', '', $customerInfo['project_manager']], $filterSpaceElement(3)),
                // 第八行相应角色电话
                array_merge(['', '联系电话：', '', $customerInfo['customer_manager_phone']], $filterSpaceElement(9), ['联系电话：', '', $customerInfo['designer_phone']], $filterSpaceElement(6), ['联系电话：', '', $customerInfo['project_manager_phone']], $filterSpaceElement(3)),
                // 第九行项目地址
                array_merge(['', '工程地址：', '', $customerInfo['address'] . $customerInfo['address_detail'] . ' ' . $order['property_name']], $filterSpaceElement(24)),
                // 第十行 空行
                $filterSpaceElement(28),
                // 第十一行 表头
                ['序号', '区域', '', '品名', '', '厂家', '', '', '工厂型号', '', '', '款型图片', '', '规格', '', '材质', '', '', '', '单位', '数量', '单价', '', '合计', '', '备注信息', '', '',],
            ];


            // 对单元格设置
            $options = [
                //'setDefaultFontName'=>'宋体',// 默认全局字体
                //            'fontName'=>'微软雅黑',
                'bold'            => ['B9', 'A11:AB11'],
                'setDefaultWidth' => 8, // 针对所有单元格统一设置，
                //            'setWidth' => [  // 针对单独单元格设置
                //            ],
                'setHeight'       => ['0' => 5, '1' => 15, '2' => 15], // 设置某些行的高度
                'freezePane'      => 'A12',
                //            'setHyperlink' => [
                //                'column'   => ['A'],
                //                'skip'      => ['A1', 'A' . $countData]
                //            ],
                'setImage'        => [
                    'column'    => ['L'],
                    'cellStyle' => false,
                    'skip'      => [
                        'L1',
                        'L2',
                        'L3',
                        'L4',
                        'L5',
                        'L6',
                        'L7',
                        'L8',
                        'L9',
                        'L10',
                        'L11'
                    ],
                ],
                'setLocalImage'   => [
                    'column'       => ['B2'], // 如果这里指定要填充的单元格，指定的单元格将使用 image_path的图片填充
                    'image_set'    => [
                        'image_path'     => public_path('images/logo.png'),
                        //                    'image_width' => 135,
                        //                    'image_height' => 45,
                        'image_offset_x' => 0,
                        'image_offset_y' => -10,
                    ],
                    'column_image' => [], // 这里面指定单元格=> 图片地址， 如： A1=>'/dev/image/a.jpg' 表示对某个单元格指定不同的图片填充
                ],
                'mergeCells'      => [
                    'A2:AB3',
                    'B5:C5',
                    'N5:O5',
                    'B7:C7',
                    'N7:O7',
                    'W7:X7',
                    'B8:C8',
                    'N8:O8',
                    'W8:X8',
                    'B9:C9',
                    'D9:M9',
                    'B11:C11',
                    'D11:E11',
                    'F11:H11',
                    'I11:K11',
                    'L11:M11',
                    'N11:O11',
                    'P11:S11',
                    'V11:W11',
                    'X11:Y11',
                    'Z11:AB11',
                ],
                'alignCenter'     => [
                    'A2',
                    'B5',
                    'N5',
                    'B7',
                    'N7',
                    'W7',
                    'B8',
                    'N8',
                    'W8',
                    'B9',
                    'A11:AB11',
                ],
                'fontSize'        => [
                    'A2' => 20
                ],
                'fontColor'       => [
                    'D5' => 'FFFF0000'
                ]
                // 'formula' => ['A1' => '="<table><img src="http://avatar.u.excelhome.net/001/54/14/97_avatar_middle.jpg">"'],
            ];

            // 开始组装数据
            $row = count($data);
            unset($order, $orderAddon, $customerInfo);
            $sumCount = $sumCost = $sumTotal = 0;
            foreach ($orderInfo['order_list'] as $order) {
                $sumCount += $order['buy_count'];
                $sumCost += $order['provider_total_price'];
                $sumTotal += $order['customer_total_cost_price'];
                $data[$row] = [
                    $order['order_provider_id'],
                    $order['extension_data']['product_attr']['space_name'],
                    '',
                    $order['extension_data']['product_attr']['type'],
                    '',
                    $order['provider_name'],
                    '',
                    '',
                    $order['product_number_system'],
                    '',
                    '',
                    $order['product_image'],
                    '',
                    $order['extension_data']['sku_attr']['specification'],
                    '',
                    $order['extension_data']['product_attr']['material'],
                    '',
                    '',
                    '',
                    '件',
                    $order['buy_count'],
                    $order['customer_cost_price'],
                    '',
                    $order['customer_total_cost_price'],
                    '',
                    '',
                    '',
                    '',
                ];

                $row++;
                // 对单元格设置合并等属性
                $options['mergeCells'] = array_merge($options['mergeCells'], [
                    "B{$row}:C{$row}",
                    "D{$row}:E{$row}",
                    "F{$row}:H{$row}",
                    "I{$row}:K{$row}",
                    "L{$row}:M{$row}",
                    "N{$row}:O{$row}",
                    "P{$row}:S{$row}",
                    "V{$row}:W{$row}",
                    "X{$row}:Y{$row}",
                    "Z{$row}:AB{$row}",
                ]);
                $options['alignCenter'] = array_merge($options['alignCenter'], ["A{$row}:AB{$row}"]);
                // 设置数据行高度
                $options['setHeight'][strval($row)] = 80;
            }
            $data[$row] = array_merge(['合计', '', ''], $filterSpaceElement(17), [$sumCount], $filterSpaceElement(2), [$sumCost, ''], $filterSpaceElement(3));
            // 最后一行不读取图片
            $row++;
            $options['setImage']['skip'] = array_merge($options['setImage']['skip'], ["L{$row}"]);
            $options['mergeCells'] = array_merge($options['mergeCells'], [
                "A{$row}:C{$row}",
                "D{$row}:E{$row}",
                "F{$row}:H{$row}",
                "I{$row}:K{$row}",
                "L{$row}:M{$row}",
                "N{$row}:O{$row}",
                "P{$row}:S{$row}",
                "V{$row}:W{$row}",
                "X{$row}:Y{$row}",
                "Z{$row}:AB{$row}",
            ]);
            $options['alignCenter'] = array_merge($options['alignCenter'], ["A{$row}:AB{$row}"]);
            try {
                $excelObj = new MTExcel();
                $data = $excelObj->generateExcelFormat($data);
                $createTime = $orderInfo['order_list'][0]['created_at'] ?? date('YmdHis');
                $redisKey = md5('cache_order_list_excel_content_for_' . $orderInfo['sn'] . $createTime . strval(date('YmdHis')));
                $saveData = [
                    'file_name' => '【清单导出】-『' . $orderInfo['provider_name'] . '』-' . date('Y年m月d日') . '.xlsx',
                    'content'   => $data,
                    'options'   => $options
                ];
                Redis::set($redisKey, json_encode($saveData, 320));
                Redis::expire($redisKey, 300);
            } catch (\Exception $e) {
            }
        }

        return $redisKey;
    }
}
